using System;
using System.Data.Common;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using ASPNETVN.PORTAL.Components.Helpers;
using System.Data.SqlClient;

namespace CUSTOMS.Components.SLXNK
{
    public class ToKhaiMauDich
    {
        public static bool KiemTra(int soToKhai, string maLoaiHinh, string maHaiQuan, int namDangKy)
        {
            bool result = false; ;
            StringBuilder query = new StringBuilder(string.Empty);
            query.Append("SELECT 1 ");
            query.Append("FROM ");
            query.Append("DTOKHAIMD TK ");
            query.Append("WHERE ");
            query.Append("TK.SoTK = @SoToKhai AND ");
            query.Append("TK.Ma_LH = @MaLoaiHinh AND ");
            query.Append("TK.Ma_HQ = @MaHaiQuan AND ");
            query.Append("TK.NamDK = @NamDangKy");

            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase("SLXNK");
            SqlCommand dbCommand = (SqlCommand)db.GetSqlStringCommand(query.ToString());

            db.AddInParameter(dbCommand, "@SoToKhai", SqlDbType.Int, soToKhai);
            db.AddInParameter(dbCommand, "@MaLoaiHinh", SqlDbType.VarChar, maLoaiHinh);
            db.AddInParameter(dbCommand, "@MaHaiQuan", SqlDbType.VarChar, maHaiQuan);
            db.AddInParameter(dbCommand, "@NamDangKy", SqlDbType.Int, namDangKy);

            IDataReader reader = db.ExecuteReader(dbCommand);
            if (reader.Read())
            {
                result = true;
            }
            reader.Close();
            return result;
        }

        public static IDataReader TimKiem(int soToKhai, string maLoaiHinh, string maHaiQuan, int namDangKy)
        {
            StringBuilder query = new StringBuilder(string.Empty);
            query.Append("SELECT ");
            query.Append("TK.* ");
            query.Append("FROM ");

            query.Append("dbo.DTOKHAIMD TK ");
            
            query.Append("WHERE ");
            
            query.Append("TK.SoTK = @SoToKhai AND ");
            query.Append("TK.Ma_LH = @MaLoaiHinh AND ");
            query.Append("TK.Ma_HQ = @MaHaiQuan AND ");
            query.Append("TK.NamDK = @NamDangKy");

            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase("SLXNK");
            SqlCommand dbCommand = (SqlCommand)db.GetSqlStringCommand(query.ToString());

            db.AddInParameter(dbCommand, "@SoToKhai", SqlDbType.Int, soToKhai);
            db.AddInParameter(dbCommand, "@MaLoaiHinh", SqlDbType.VarChar, maLoaiHinh);
            db.AddInParameter(dbCommand, "@MaHaiQuan", SqlDbType.VarChar, maHaiQuan);
            db.AddInParameter(dbCommand, "@NamDangKy", SqlDbType.VarChar, namDangKy);

            return db.ExecuteReader(dbCommand);
        }

        public static DataSet TimKiem(string maDoanhNghiep, int soToKhai, string maLoaiHinh, string maHaiQuan, int namDangKy, DateTime tuNgay, DateTime denNgay)
        {
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase("SLXNK");
            SqlCommand dbCommand = new SqlCommand();

            StringBuilder query = new StringBuilder(string.Empty);
            query.Append("SELECT TK.SoTK AS SoToKhai, ");
            query.Append("TK.Ngay_DK AS NgayDangKy, ");
            query.Append("TK.NamDK AS NamDangKy, ");
            query.Append("TK.Ma_LH AS MaLoaiHinh, ");
            query.Append("LH.Ten_LH AS TenLoaiHinh, ");
            query.Append("TK.Ma_HQ AS MaHaiQuan, ");
            query.Append("HQ.Ten_HQ AS TenHaiQuan ");
            query.Append("FROM ");
            query.Append("DToKhaiMD TK ");
            query.Append("INNER JOIN SLHINHMD LH ON TK.MA_LH = LH.MA_LH ");
            query.Append("INNER JOIN SHAIQUAN HQ ON TK.MA_HQ = HQ.MA_HQ ");

            query.Append("WHERE ");
            if (maDoanhNghiep.Length > 0)
            {
                query.Append(string.Format("TK.Ma_DV = @MaDoanhNghiep AND ", maDoanhNghiep));
                db.AddInParameter(dbCommand, "@MaDoanhNghiep", SqlDbType.VarChar, maDoanhNghiep);
            }

            if (soToKhai > 0)
            {
                query.Append(string.Format("TK.SoTK = @SoToKhai AND ", soToKhai));
                db.AddInParameter(dbCommand, "@SoToKhai", SqlDbType.Int, soToKhai);
            }

            // Loai hinh.
            if (string.IsNullOrEmpty(maLoaiHinh) == false)
            {
                query.Append(string.Format("TK.Ma_LH LIKE @MaLoaiHinh AND ", maLoaiHinh));
                db.AddInParameter(dbCommand, "@MaLoaiHinh", SqlDbType.VarChar, maLoaiHinh);
            }

            // Hai quan mo to khai.
            if (string.IsNullOrEmpty(maHaiQuan) == false)
            {
                query.Append(string.Format("TK.Ma_HQ LIKE @MaHaiQuan AND ", maHaiQuan));
                db.AddInParameter(dbCommand, "@MaHaiQuan", SqlDbType.VarChar, maHaiQuan);
            }

            // Nam dang ky.
            if (namDangKy > 1900)
            {
                query.Append(string.Format("TK.NamDK = @NamDangKy AND ", namDangKy));
                db.AddInParameter(dbCommand, "@NamDangKy", SqlDbType.Int, namDangKy);
            }
            // Tu ngay -> den ngay.
            if (tuNgay.Year > 1900 && denNgay.Year > 1900)
            {
                query.Append("(TK.Ngay_DK >= @TuNgay AND TK.Ngay_DK <= @DenNgay) AND ");
                db.AddInParameter(dbCommand, "@TuNgay", SqlDbType.DateTime, tuNgay);
                db.AddInParameter(dbCommand, "@DenNgay", SqlDbType.DateTime, denNgay);
            }
            query.Append("1 = 1 ");

            query.Append("ORDER BY TK.Ngay_DK DESC, TK.SoTK DESC, TK.Ma_HQ, TK.Ma_LH");

            try
            {
                dbCommand.Connection = (SqlConnection) db.CreateConnection();
                dbCommand.CommandText = query.ToString();
                DataSet ds = db.ExecuteDataSet(dbCommand);
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    row["TenLoaiHinh"] = FontHelper.TCVN2Unicode(row["TenLoaiHinh"].ToString());
                    row["TenHaiQuan"] = FontHelper.TCVN2Unicode(row["TenHaiQuan"].ToString());
                }
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + ": " + query.ToString());
            }
        }
    }
}
